﻿using System;
using System.Configuration;
using System.Data;
using System.Data.OracleClient;
using System.Collections;
using System.Collections.Generic;

namespace TerminologiaSinacor.Presenter.DAL.Oracle
{
	public abstract class 	OracleHelper
	{
		#region connect

		public static IDbConnection connect(String connectionString)
		{
			return new OracleConnection(connectionString) as IDbConnection;
		}

		#endregion

		#region executeNonQuery

		public static int executeNonQuery(string connString, CommandType cmdType, string cmdText, params IDataParameter[] cmdParms)
		{
			OracleCommand cmd = new OracleCommand();

			using (OracleConnection conn = new OracleConnection(connString))
			{
				prepareCommand(cmd, (IDbConnection)conn, null, cmdType, cmdText, cmdParms);

				int val = cmd.ExecuteNonQuery();
				cmd.Parameters.Clear();
				return val;
			}
		}

		public static int executeNonQuery(IDbTransaction trans, CommandType cmdType, string cmdText, params IDataParameter[] cmdParms)
		{
			OracleCommand cmd = new OracleCommand();
			prepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
			int val = cmd.ExecuteNonQuery();
			cmd.Parameters.Clear();
			return val;
		}

		public static int executeNonQuery(IDbConnection conn, CommandType cmdType, string cmdText, params IDataParameter[] cmdParms)
		{

			OracleCommand cmd = new OracleCommand();

			prepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
			int val = cmd.ExecuteNonQuery();
			cmd.Parameters.Clear();
			return val;
		}
		
		#endregion

		#region executeReader

		public static IDataReader executeReader(string connString, CommandType cmdType, string cmdText, params IDataParameter[] cmdParms)
		{
			OracleCommand cmd = new OracleCommand();
			OracleConnection conn = new OracleConnection(connString);

			try
			{
				prepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);

				OracleDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
				cmd.Parameters.Clear();
				return rdr as IDataReader;

			}
			catch (Exception e)
			{
				conn.Close();
				throw e;
			}
		}

		public static IDataReader executeReader(IDbTransaction trans, CommandType cmdType, string cmdText, params IDataParameter[] cmdParms)
		{
			OracleCommand cmd = new OracleCommand();
			prepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
			OracleDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
			cmd.Parameters.Clear();
			return rdr as IDataReader;
		}
		
		#endregion

		#region executeScalar

		public static object executeScalar(string connString, CommandType cmdType, string cmdText, params IDataParameter[] cmdParms)
		{
			OracleCommand cmd = new OracleCommand();

			using (OracleConnection conn = new OracleConnection(connString))
			{
				prepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
				object val = cmd.ExecuteScalar();
				cmd.Parameters.Clear();
				return val;
			}
		}

		public static object executeScalar(IDbConnection conn, CommandType cmdType, string cmdText, params IDataParameter[] cmdParms)
		{

			OracleCommand cmd = new OracleCommand();

			prepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
			object val = cmd.ExecuteScalar();
			cmd.Parameters.Clear();
			return val;
		}

		public static object executeScalar(IDbTransaction trans, CommandType cmdType, string cmdText, params IDataParameter[] cmdParms)
		{
			OracleCommand cmd = new OracleCommand();

			prepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
			object val = cmd.ExecuteScalar();
			cmd.Parameters.Clear();
			return val;
		}
		
		#endregion

		#region prepareCommand

		private static void prepareCommand(IDbCommand cmd, IDbConnection conn, IDbTransaction trans, CommandType cmdType, string cmdText, IDataParameter[] cmdParms)
		{
			if (conn.State != ConnectionState.Open)
				conn.Open();

			cmd.Connection = conn as OracleConnection;
			cmd.CommandText = cmdText;
			cmd.CommandType = cmdType;

			if (trans != null)
				cmd.Transaction = trans as OracleTransaction;

			if (cmdParms != null)
			{
				foreach (IDataParameter parm in cmdParms)
					cmd.Parameters.Add(parm);
			}
		}
		
		#endregion
	}
}

